-- @owner: zou_jialiang050
-- @date: 2024-08-12
-- @testpoint: 设置schema的搜索路径，调用函数后查看不同schema下相同表名的数据插入

SET behavior_compat_options = 'bind_procedure_searchpath';
drop schema if exists sche1 cascade;
drop schema if exists sche2 cascade;
create schema sche1;
create schema sche2;
create table sche1.bind_t1(a int);
create table sche2.bind_t1(a int);

create or replace function sche2.fun_001()
returns int
as $$
declare
begin
        insert into bind_t1 values(2);
        return 2;
end;
$$ LANGUAGE plpgsql;
/

set search_path = sche1;
call sche2.fun_001();
select * from sche1.bind_t1;

set search_path = sche2;
call sche2.fun_001();

select * from sche1.bind_t1;
select * from sche2.bind_t1;

--step2:清理环境;expect:成功
drop schema sche1 cascade;
drop schema sche2 cascade;
reset behavior_compat_options;